﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
   public class DbHelper
    {
       private SqlConnection connection;
       /// <summary>
       /// Database connection Object property
       /// </summary>
       public SqlConnection Connection
       {
           get
           {
               string connectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
               if(connection == null)
               {
                   connection = new SqlConnection(connectionString);
                   connection.Open();
               }
               else if(connection.State == System.Data.ConnectionState.Closed)
               {
                   connection.Open();
               }
               else if (connection.State == System.Data.ConnectionState.Broken)
               {
                   connection.Close();
                   connection.Open();
               }
               return connection;
           }
       }
       

       /// <summary>
       /// Close DB connection
       /// </summary>
       public void CloseDB()
       {
           if (connection.State == System.Data.ConnectionState.Open || connection.State == System.Data.ConnectionState.Broken)
           {
               connection.Close();
           }
       
       }


       public DataRow GetDataRow(string sql, params SqlParameter[] values)
       {
           using (SqlCommand cmd = Connection.CreateCommand())
           {
               cmd.CommandText = sql;
               cmd.Parameters.AddRange(values);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataSet ds = new DataSet();
               da.Fill(ds);
               if (ds.Tables[0].Rows.Count > 0)
               {
                   return ds.Tables[0].Rows[0];
               }
               else
                   return null;
           }
       }

       /// <summary>
       /// 执行sql ，返回数据表
       /// </summary>
       /// <param name="safeSql">Sql 语句</param>
       /// <returns>数据表</returns>
       public DataTable GetDataTable(string safeSql)
       { 
            DataSet ds = new DataSet();
            using(SqlCommand cmd = Connection.CreateCommand())
            {
                cmd.CommandText = safeSql;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }

            return ds.Tables[0];
       }

       public DataTable GetDataTable(string Sql,params SqlParameter[] values)
       {
           DataSet ds = new DataSet();
           using (SqlCommand cmd = Connection.CreateCommand())
           {
               cmd.CommandText = Sql;
               cmd.Parameters.AddRange(values);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               da.Fill(ds);
           }

           return ds.Tables[0];
       }

       /// <summary>
       /// 执行Command
       /// </summary>
       /// <param name="sql">sql</param>
       /// <param name="values">sql 参数数组</param>
       /// <returns></returns>
       public int ExecuteCommand(string sql, params SqlParameter[] values)
       {
           using (SqlCommand cmd = Connection.CreateCommand())
           {
               cmd.CommandText = sql;
               cmd.Parameters.AddRange(values);
               int result = cmd.ExecuteNonQuery();
               return result;
           }


       }

    }
}
